package com.hele.sts.dao;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 * @ClassName DocMoveOutDao
 * @Description TODO
 * @Author xuning
 * @Date 2019-04-02 11:17
 **/

@Repository
public class DocMoveOutDao {

    private static final Logger logger = LoggerFactory.getLogger(DocMoveOutDao.class);

    private JdbcTemplate jdbcTemplate;

    @Autowired
    public void setDataSource(DataSource dataSource) {
        this.jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public int addMoveOutDa(DocMoveOut docMoveOut){
        Calendar calendar = Calendar.getInstance();
        int year = calendar.get(Calendar.YEAR);
        String sql = "insert into da_moveout values(sq_doc_move.nextval,?,?,?,?,?,?,?,?,?,?,?,sysdate,?)";
        return jdbcTemplate.update(sql, docMoveOut.getNbxh(),docMoveOut.getReason(),
                docMoveOut.getDjjg(),docMoveOut.getTargetDjjg(),docMoveOut.getAddr(),docMoveOut.getShippingMethod(),
                docMoveOut.getFilesNum(),docMoveOut.getFilesOriginal(),docMoveOut.getJbr(),docMoveOut.getRemark(),
                docMoveOut.getQymcOut(),docMoveOut.getStatus());
    }

    public int updateMoveOutDa(DocMoveOut docMoveOut){
        String sql = "update da_moveout set reason=?,addr=?,shipping_method=?,files_num=?,files_original=?,jbr=?,remark=?,target_djjg=?,create_time=sysdate where id=?";
        Object args[] = new Object[]{docMoveOut.getReason(),docMoveOut.getAddr(),docMoveOut.getShippingMethod(),
                docMoveOut.getFilesNum(),docMoveOut.getFilesOriginal(),docMoveOut.getJbr(),docMoveOut.getRemark(),docMoveOut.getTargetDjjg(),docMoveOut.getId(),};
        return jdbcTemplate.update(sql, args);
    }

    public int deleteMoveOutDa(String id){
        String sql = "delete from da_moveout where id=?";
        Object args[] = new Object[]{id};
        return jdbcTemplate.update(sql,args);
    }

    public int queryMoveOutCount(String qymc,String uniscid,String startDate,String endDate){
        String sql = "select count(*) as cnt from da_moveout where 1=1 ";
        List<Object> argsList = new ArrayList<>();
        Object[] args = new Object[]{};
        if(qymc!=null&&!qymc.equals("")){
            sql += " and qymc_out like ?";
            argsList.add("%"+qymc+"%");
        }
        if(uniscid!=null&&!uniscid.equals("")){
            sql += " and uniscid = ?";
            argsList.add(uniscid);
        }
        if(startDate!=null&&!startDate.equals("")){
            sql += " and create_time >= to_date(?,'yyyy-MM-dd')";
            argsList.add(startDate);
        }
        if(endDate!=null&&!endDate.equals("")){
            sql += " and create_time < to_date(?,'yyyy-MM-dd')+1";
            argsList.add(endDate);
        }
        logger.debug("sql:"+sql);
        return this.jdbcTemplate.queryForObject(sql,argsList.toArray(),Integer.class);
    }

    public List<DocMoveOut> queryMoveOutList(String qymc, String uniscid, String startDate, String endDate){
        String sql = "select a.*,b.uniscid from da_moveout a,HZ_QYHZNR b where a.NBXH=b.NBXH ";
        List<Object> argsList = new ArrayList<>();
        Object[] args = new Object[]{};
        if(qymc!=null&&!qymc.equals("")){
            sql += " and qymc_out like ?";
            argsList.add("%"+qymc+"%");
        }
        if(uniscid!=null&&!uniscid.equals("")){
            sql += " and uniscid = ?";
            argsList.add(uniscid);
        }
        if(startDate!=null&&!startDate.equals("")){
            sql += " and create_time >= to_date(?,'yyyy-MM-dd')";
            argsList.add(startDate);
        }
        if(endDate!=null&&!endDate.equals("")){
            sql += " and create_time < to_date(?,'yyyy-MM-dd')+1";
            argsList.add(endDate);
        }
        logger.debug("sql:"+sql);
        return this.jdbcTemplate.query(sql, argsList.toArray(), new DocMoveOutRowMapper());

    }

    class DocMoveOutRowMapper implements RowMapper<DocMoveOut> {

        @Override
        public DocMoveOut mapRow(ResultSet rs, int rowNum) throws SQLException {
            DocMoveOut docMoveOut = new DocMoveOut();
            docMoveOut.setId(rs.getString("id"));
            docMoveOut.setNbxh(rs.getString("nbxh"));
            docMoveOut.setDjjg(rs.getString("djjg"));
            docMoveOut.setTargetDjjg(rs.getString("target_djjg"));
            docMoveOut.setQymcOut(rs.getString("qymc_out"));
            docMoveOut.setCreateTime(rs.getDate("create_time"));
            docMoveOut.setStatus(rs.getInt("status"));
            docMoveOut.setFilesNum(rs.getInt("files_num"));
            docMoveOut.setFilesOriginal(rs.getInt("files_original"));
            docMoveOut.setJbr(rs.getString("jbr"));
            docMoveOut.setRemark(rs.getString("remark"));
            docMoveOut.setShippingMethod(rs.getString("shipping_method"));
            docMoveOut.setReason(rs.getString("reason"));
            docMoveOut.setAddr(rs.getString("addr"));
            return docMoveOut;
        }
    };

    public DocMoveOut queryById(String id) {
        String sql = "select * from da_moveout where id = ? ";
        RowMapper<DocMoveOut> rowMapper = new BeanPropertyRowMapper<>(DocMoveOut.class);
        List<DocMoveOut> docMoveOutList = jdbcTemplate.query(sql, rowMapper, id);

        if(docMoveOutList.size()==0){
            return null;
        }

        return docMoveOutList.get(0);
    }

    public DocMoveOut queryByNbxh(String nbxh) {
        String sql = "select * from da_moveout where nbxh = ? ";
        RowMapper<DocMoveOut> rowMapper = new BeanPropertyRowMapper<>(DocMoveOut.class);
        List<DocMoveOut> docMoveOutList = jdbcTemplate.query(sql, rowMapper, nbxh);

        if(docMoveOutList.size()==0){
            return null;
        }

        return docMoveOutList.get(0);
    }
}
